/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.myapp.struts.dao;

//~--- JDK imports ------------------------------------------------------------
import com.myapp.struts.actionform.EditMaterialTypeFormBean;
import com.myapp.struts.actionform.MaterialFormBean;
import com.myapp.struts.beans.ItemBean;
import com.myapp.struts.beans.ItemGroupBean;
import com.myapp.struts.beans.SingleItemBean;
import com.myapp.struts.prepare.PrepareItemMaster;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.sql.SQLException;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.struts.action.ActionForm;

/**
 *
 * @author pavan
 */
public class MaterialMasterDao {

    private String checkgroupid;
    private Connection con;
    private String deleteGroupQuery;
    private String deleteGroupQuery1;
    private Connect drv;
    private ResultSet findGroup;
    private String getGroup;
    private String getItems;
    private String getType;
    private ResultSet groupItems;
    private int group_no;
    private int group_num;
    private String insertItem;
    private String insert_type;
    private String item_group;
    private int item_group_id;
    private String item_type;
    private int item_type_id;
    private PrepareItemMaster pitype;
    private PreparedStatement pstmt;
    private int status;
    private int status1;
    private ResultSet typeItems;
    private int type_no;
    private Vector vec, items;
    private ResultSet ItemsRs;
    private String deleteItemsQuery;
    private String[] delete_items;
    private String getItemCode;
    private ResultSet itemCode;
    private MaterialFormBean form1;
    private String updateType;
    private EditMaterialTypeFormBean emtFb;

    public MaterialMasterDao() {
        vec = new Vector();
        items = new Vector();
        drv = new Connect();
        form1 = new MaterialFormBean();
    }

    public boolean delete(ItemGroupBean igbean) {
        try {
            con = getConnect();
            con.setAutoCommit(false);
            group_no = getGroup(igbean.getGroup_name());
            checkgroupid = "select * from items where item_group_id=?";
            pstmt = con.prepareStatement(checkgroupid);
            pstmt.setInt(1, group_no);
            findGroup = pstmt.executeQuery();

            if (findGroup.next() == true) {
                System.out.println("In true");
                deleteGroupQuery = "delete from items where item_group_id=?";
                pstmt = con.prepareStatement(deleteGroupQuery);
                pstmt.setInt(1, group_no);
                status = 0;
                status = pstmt.executeUpdate();
                con.commit();
                System.out.println("status=" + status);

                if (status >= 1) {
                    System.out.println("In true 2");
                    System.out.println("ItemS Group Deleted");
                    deleteGroupQuery1 = "delete from igroup where item_group_id=?";
                    pstmt = con.prepareStatement(deleteGroupQuery1);
                    pstmt.setInt(1, group_no);
                    status1 = pstmt.executeUpdate();

                    if (status1 >= 1) {
                        con.commit();
                        con.close();
                        pstmt.close();

                        return true;
                    } else {
                        System.out.println("In false 1");
                        pstmt.close();
                        con.rollback();
                        con.close();

                        return false;
                    }
                } else {
                    System.out.println("In status false 1");
                    pstmt.close();
                    con.rollback();
                    con.close();

                    return false;
                }
            } else {
                System.out.println("in false");
                deleteGroupQuery1 = "delete from igroup where item_group_id=?";
                pstmt = con.prepareStatement(deleteGroupQuery1);
                pstmt.setInt(1, group_no);
                status1 = pstmt.executeUpdate();

                if (status1 == 1) {
                    System.out.println("In delete Success");
                    con.commit();
                    con.close();
                    pstmt.close();

                    return true;
                } else {
                    pstmt.close();
                    con.rollback();
                    con.close();

                    return false;
                }
            }
        } catch (Exception ex) {
            Logger.getLogger(MaterialMasterDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    public boolean delete(SingleItemBean sibean) {
        try {
            con = getConnect();
            con.setAutoCommit(true);
            delete_items = sibean.getItem_name1();
            for (int i = 0; i < delete_items.length; i++) {
                System.out.println(delete_items[i]);
                deleteItemsQuery = "delete from items where item_code=?";
                pstmt = con.prepareStatement(deleteItemsQuery);
                pstmt.setString(1, delete_items[i]);
                status = 0;
                status = pstmt.executeUpdate();
            }
            con.close();
            pstmt.close();
            System.out.println("Status=" + status);
            if (status >= 1) {
                return true;
            } else {
                return false;
            }

        } catch (Exception ex) {
            Logger.getLogger(MaterialMasterDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public boolean editMaterialType(ActionForm form) {
        try {
            /** Type cast ActionForm variable to FormBean */
            emtFb = (EditMaterialTypeFormBean) form;
            /** Take Database Connection */
            con = drv.mySql();
            updateType = "update materialtype set materialtype=? where materialtype=?";
            pstmt = con.prepareStatement(updateType);
            pstmt.setString(1, emtFb.getNewType());
            pstmt.setString(2, emtFb.getTypeEdit());
            status = pstmt.executeUpdate();
            if (status == 1) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(MaterialMasterDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public Connection getConnect() throws Exception {
        drv = new Connect();
        con = drv.mySql();
        pitype = new PrepareItemMaster();
        vec = new Vector();

        return con;
    }

    public Vector getItems(ItemBean ibean) {
        try {
            con = getConnect();
            item_group = ibean.getItem_group();

            item_group_id = getGroup(item_group);
            System.out.println("item_group" + item_group_id);
            getItems = "select item_code,itemname,qty,uom,rate from items where item_group_id=?";
            pstmt = con.prepareStatement(getItems);
            pstmt.setInt(1, item_group_id);
            ItemsRs = pstmt.executeQuery();
            items = pitype.prepareItemName(ItemsRs);
            con.close();
            pstmt.close();
        } catch (Exception ex) {
            Logger.getLogger(MaterialMasterDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return items;
    }

    public Vector getTypes() {
        try {
            con = getConnect();
            getType = "select distinct(materialtype) from materialtype";
            pstmt = con.prepareStatement(getType);
            typeItems = pstmt.executeQuery();
            vec = pitype.prepareTypeList(typeItems);
            con.close();
            pstmt.close();
        } catch (Exception ex) {
            Logger.getLogger(MaterialMasterDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return vec;
    }

    public Vector getGroups() {
        try {
            con = getConnect();
            getGroup = "select distinct(item_group_name) from igroup";
            pstmt = con.prepareStatement(getGroup);
            groupItems = pstmt.executeQuery();
            vec = pitype.prepareGroupList(groupItems);
            con.close();
            pstmt.close();
        } catch (Exception ex) {
            Logger.getLogger(MaterialMasterDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return vec;
    }

    public Vector getItems() {
        try {
            con = getConnect();
            getItems = "select distinct(itemname) from items";
            pstmt = con.prepareStatement(getItems);
            groupItems = pstmt.executeQuery();
            vec = pitype.prepareItem(groupItems);
            con.close();
            pstmt.close();
        } catch (Exception ex) {
            Logger.getLogger(MaterialMasterDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return vec;
    }

    public Vector getItemCodes() {
        try {
            con = getConnect();
            getItemCode = "select distinct(item_code) from items";
            pstmt = con.prepareStatement(getItemCode);
            itemCode = pstmt.executeQuery();
            vec = pitype.prepareItem(itemCode);
            con.close();
            pstmt.close();
        } catch (Exception ex) {
            Logger.getLogger(MaterialMasterDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return vec;
    }

    public int getType(String type_name) {
        try {
            System.out.println("Type_name" + type_name);
            getType = "select distinct(ino) from itypes where itype='" + type_name + "'";
            pstmt = con.prepareStatement(getType);
            typeItems = pstmt.executeQuery();

            if (typeItems.next() == true) {
                type_no = typeItems.getInt(1);
            }

            System.out.println("Type_no" + type_no);
            pstmt.close();
        } catch (Exception ex) {
            Logger.getLogger(MaterialMasterDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return type_no;
    }

    public int getGroup(String group_name) {
        try {
            System.out.println("Type_name" + group_name);
            getGroup = "select distinct(item_group_id) from igroup where item_group_name='" + group_name + "'";
            System.out.print("get Group =" + getGroup);
            pstmt = con.prepareStatement(getGroup);
            groupItems = pstmt.executeQuery();

            if (groupItems.next() == true) {
                group_no = groupItems.getInt(1);
            }

            System.out.println("Type_no" + group_no);
            pstmt.close();
        } catch (Exception ex) {
            Logger.getLogger(MaterialMasterDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return group_no;
    }

    public boolean insertItems(ActionForm form) {
        try {
            form1 = (MaterialFormBean) form;
            con = getConnect();
            item_type = form1.getItemType();
            String itemCode = form1.getItemCode();
            String itemName = form1.getItemName();
            double qty = form1.getQuantity();
            double rate = form1.getItemRate();
            String uom = form1.getUnitOfMeasurement();


            insertItem = "insert into items values(?,?,?,?,?,?)";
            pstmt = con.prepareStatement(insertItem);
            pstmt.setString(1, item_type);
            pstmt.setString(2, itemCode);
            pstmt.setString(3, itemName);
            pstmt.setDouble(4, qty);
            pstmt.setString(5, uom);
            pstmt.setDouble(6, rate);
            status = pstmt.executeUpdate();
            if (status >= 1) {
                con.close();
                pstmt.close();
                System.out.println("Insert Group Close");

                return true;
            } else {
                return false;
            }
        } catch (Exception ex) {
            Logger.getLogger(MaterialMasterDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    public boolean insert(ActionForm form) {
        try {
            /** Type cast ActionForm variable to FormBean */
            form1 = (MaterialFormBean) form;

            /** Take Database Connection */
            con = drv.mySql();
            insert_type = "Insert into materialtype values(?,?)";
            pstmt = con.prepareStatement(insert_type);
            pstmt.setString(1, form1.getMaterialType());
            pstmt.setString(2, form1.getDescription());
            status = pstmt.executeUpdate();

            if (status == 1) {
                con.close();
                pstmt.close();
                return true;
            } else {
                return false;
            }
        } catch (Exception ex) {
            Logger.getLogger(MaterialMasterDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return false;
    }

    public boolean update(ItemBean ibean) {
        try {
            con = getConnect();
            for (int i = 0; i < ibean.getItem_code().length; i++) {
                pstmt = con.prepareStatement("update items set itemname=?,qty=?,uom=?,rate=?"
                        + " where materialtype=? and item_code=?");
                pstmt.setString(1, ibean.getItem_name()[i]);
                pstmt.setString(2, ibean.getQty()[i]);
                pstmt.setString(3, ibean.getUom()[i]);
                pstmt.setString(4, ibean.getRate()[i]);
                pstmt.setString(5, ibean.getItem_type());
                pstmt.setString(6, ibean.getItem_code()[i]);
                status = pstmt.executeUpdate();
            }
            if (status == 1) {
                con.close();
                pstmt.close();
                return true;
            } else {
                return false;
            }

        } catch (Exception ex) {
            Logger.getLogger(MaterialMasterDao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
}
